Excel BI - Excel Challenge 807

excel-challenges
excel-formulas
🔰 C1 C2 C3 C4 C5 C6 Answer Expected Fill in the grid with the immediate non-blank value on the left.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 807

Challenge Description

🔰 C1 C2 C3 C4 C5 C6 Answer Expected Fill in the grid with the immediate non-blank value on the left. Traversing the grid from left to right, fill in a cell from immediate past odd number.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/807/807 Fill in With Previous Odd Number.xlsx"
input = read_excel(path, sheet = 2, range = "A2:F10", col_names = FALSE) %>% as.matrix()
test  = read_excel(path, sheet = 2, range = "H2:M10", col_names = FALSE) %>% as.matrix()

df = as.data.frame(as.vector(t(input)))
colnames(df) = "V1"

r1 = df %>%
  mutate(V2 = ifelse(V1 %% 2 == 1, V1, NA)) %>%
  fill(V2) %>%
  mutate(V2 = ifelse(is.na(V1), V2, V1)) %>%
  pull(V2) %>% 
  matrix(nrow = nrow(input), byrow = TRUE)

r1 == test
# one cell is missing in input (with even value)
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Apply the business rule conditions explicitly.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd
import numpy as np

path = "800-899/807/807 Fill in With Previous Odd Number.xlsx"

input_mat = pd.read_excel(path, sheet_name=1, usecols="A:F", skiprows=1, nrows=9, header=None).values
test_mat = pd.read_excel(path, sheet_name=1, usecols="H:M", skiprows=1, nrows=9, header=None).values

flat = input_mat.flatten()
v2 = np.where(flat % 2 == 1, flat, np.nan)
v2 = pd.Series(v2).ffill().values
v2 = np.where(~np.isnan(flat), flat, v2)
r1 = v2.reshape(input_mat.shape)

comparison = (r1 == test_mat)

print(comparison) # One field has empty value in input matrix

The Python version mirrors the same workbook logic with a concise, direct implementation.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.